<?php
/**
 * Bookings Model for RoomBooking Component
 * 
 * @version    1.0
 * @package    Joomla
 * @subpackage RoomBooking
 * @copyright  (C) 2011 Giovanni Marzulli
 * @license	   GNU/GPL
 */
 
// No direct access
defined('_JEXEC') or die();
 
jimport( 'joomla.application.component.model' );
 
class RoomBookingModelBookings extends JModel
{
    /**
     * Booking data array
     *
     * @var array
     */
    var $_data;
 
    /**
     * Returns the query
     * @return string The query to be used to retrieve the rows from the database
     */
    function _buildQuery()
    {
        $query = ' SELECT r.id, r.short_description, r.description, date_format( r.start, "%Y-%m-%d %H:%i" ) as start, date_format( r.end, "%Y-%m-%d %H:%i" ) as end, '.
					' r.id_user, room.name AS room_name, r.series, room.id AS room_id,r.total FROM #__roombooking_bookings AS r LEFT OUTER JOIN '.
					' #__roombooking_rooms AS room ON r.id_room = room.id WHERE start >= CURDATE() ORDER BY start' ;
        return $query;
    }
 
    /**
     * Retrieves future bookings
     * @return array Array of objects containing the data from the database
     */
    function getData()
    {
        // Lets load the data if it doesn't already exist
        if (empty( $this->_data ))
        {
            $query = $this->_buildQuery();
            $this->_data = $this->_getList( $query );
        }
 
        return $this->_data;
    }
    
     /**
     * Retrieves past and future bookings
     * @return array Array of objects containing the data from the database
     */
    function getAllBookings(){
   		$query = ' SELECT r.id, r.short_description, r.description, date_format( r.start, "%Y-%m-%d %H:%i" ) as start, date_format( r.end, "%Y-%m-%d %H:%i" ) as end, '.
					' r.id_user, r.repeat_type, r.repeat_until, r.series, room.name AS room_name, room.id AS room_id FROM #__roombooking_bookings AS r LEFT OUTER JOIN '.
					' #__roombooking_rooms AS room ON r.id_room = room.id ORDER BY start' ;

    
 		$bookings = $this->_getList( $query );
 		return $bookings;
    }
    
	 /**
     * Retrieves expired bookings
     * @return array Array of objects containing the data from the database
     */
    function getExpBookings(){
   		$query = ' SELECT r.id, r.short_description, r.description, date_format( r.start, "%Y-%m-%d %H:%i" ) as start, date_format( r.end, "%Y-%m-%d %H:%i" ) as end, '.
					' r.id_user, r.repeat_type, r.repeat_until, r.series, room.name AS room_name, room.id AS room_id FROM #__roombooking_bookings AS r LEFT OUTER JOIN '.
					' #__roombooking_rooms AS room ON r.id_room = room.id WHERE start < CURDATE() ORDER BY start' ;

    
 		$bookings = $this->_getList( $query );
 		return $bookings;
    }
    
    function getInfoCsv(){
        $id_user = JRequest::getVar('id_user');
        if ($id_user == null)
            $id_user = 0;
        $month = JRequest::getVar('month');
        if ($month == null)
            $month = 0;
        $year = JRequest::getVar('year');
        if ($year == null)
            $year = 0;

        $where = '';
        if ($id_user != 0) {
            $where = ' where ';
            $id_user_str = ' id_user = ' . $id_user . ' ';
        }else
            $id_user_str = '';
        if ($year != 0) {
            $where = ' where ';
            if ($id_user != 0)
                $year_str = ' AND year = ' . $year . ' ';
            else
                $year_str = ' year = ' . $year . ' ';
        }else
            $year_str = '';
        if ($month != 0) {
            $where = ' where ';
            if ($id_user != 0 || $year != 0)
                $month_str = ' AND month = ' . $month . ' ';
            else
                $month_str = ' month = ' . $month . ' ';
        }else
            $month_str = '';

        $query = 'select c.id,c.id_user,c.year,c.month,SUM(c.cost) as cost,SUM(c.charge) as charge,u.name,u.username,u.address,u.phone from #__roombooking_costs AS c LEFT JOIN #__users as u ON c.id_user = u.id ' . $where . ' ' . $id_user_str . ' ' . $year_str . ' ' . $month_str . ' group by id_user,year,month order by year,month';

        return $this->_getList( $query );
    }
    
    /**
     * Retrieves future bookings of room different of booking with id=$id_booking
     * @return array Array of objects containing the data from the database
     */
	function getRoomBookings($id_room, $id_booking){
		$query = 'SELECT start, end FROM #__roombooking_bookings WHERE start >= CURDATE() AND id_room = '.$id_room.' AND id <> '.$id_booking;
		$bookings = $this->_getList( $query );
		
		return $bookings;
	}
}